﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using BCNPCObject.DB;
using DbLinq;
using DbLinq.Oracle;
using CMISLibrary.Entities;
using CMISLibrary;

namespace BCNPCObject.EXCEL
{
    public class cls_NPC_CHAMNO_OFFLINE_LOG_Controller
    {

        #region   Atributes

        //private DataSet CMIS_Header = new CMISOutputParameter();
        private NPC_CHAMNO_OFFLINE_LOG info;

        #endregion

        #region   Constructor
        public cls_NPC_CHAMNO_OFFLINE_LOG_Controller()
        {
            info = new NPC_CHAMNO_OFFLINE_LOG();
        }
        public cls_NPC_CHAMNO_OFFLINE_LOG_Controller(NPC_CHAMNO_OFFLINE_LOG Info)
        {
            this.info = Info;
        }

        #endregion

        #region   Properties

        public NPC_CHAMNO_OFFLINE_LOG pInfor
        {
            get { return info; }
            set { info = value; }
        }
        #endregion

        #region Methods
        public bool insert_NPC_CHAMNO_OFFLINE_LOG(ref string maloi)
        {
            long _ID = CMIS2.DB.ExecuteCommand("Select SEQ_NPC_CHAMNO_OFFLINE_LOG.NEXTVAL from dual");
            if (_ID == -1) return false;
            try
            {
                info.ID_LOG = Convert.ToInt64(_ID);
                info.NGAY_TAO = System.DateTime.Now;
                info.GIO = System.DateTime.Now.Hour;
                NPC_CHAMNO_OFFLINE_LOG objNPC_CHAMNO_OFFLINE_LOG = new NPC_CHAMNO_OFFLINE_LOG()
                {
                    MA_DVIQLY = info.MA_DVIQLY,
                    MA_KHANG = info.MA_DVIQLY,
                    ID_LOG = info.ID_LOG,
                    ID_HDON = info.ID_HDON,
                    THANG_HT = info.THANG_HT,
                    NAM_HT = info.NAM_HT,
                    MA_TNGAN = info.MA_TNGAN,
                    NGAY_CHAMNO = info.NGAY_CHAMNO,
                    TRANGTHAI = info.TRANGTHAI,
                    GIO = info.GIO,
                    NGAY_TAO = info.NGAY_TAO,
                    NGUOI_TAO = info.NGUOI_TAO,
                };
                CMIS2.DB.NPC_CHAMNO_OFFLINE_LOG.InsertOnSubmit(info);
                CMIS2.DB.SubmitChanges();
                //id_ls_treothao = _ID;
                CMIS2.ResetDB();
                return true;
            }
            catch (Exception ex)
            {
                maloi = ex.ToString();
                CMIS2.ResetDB();
                return false;
            }
        }
        public DataSet get_DSLOG(string strMadvi,string ma_khang,Int64 id_hdon, string strMatngan, Int16 ithanght, Int16 inamht, string pNgayChamNo,int tugio,int dengio, int Danhsach, int IN_BC)
        {
            try
            {
                List<D_TNGAN_VIEN> d_tnganvien_lst = new List<D_TNGAN_VIEN>();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                CMIS2.ResetDB();
                //List<NPC_CHAMNO_OFFLINE_LOG> npc_chamnooffline_log_list = new List<NPC_CHAMNO_OFFLINE_LOG>();
                //List<NPC_CHAMNO_OFFLINE_LOG> npc_chamnooffline = new List<NPC_CHAMNO_OFFLINE_LOG>();
                Int64[] arrID_HDON;
               // var loglist = null;
                d_tnganvien_lst = (from q in CMIS2.DB.D_TNGAN_VIEN
                                   where q.MA_DVIQLY == strMadvi
                                   select q).ToList();
                var temp = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE_LOG
                                      where p.MA_DVIQLY == strMadvi
                                      && p.THANG_HT == ithanght && p.NAM_HT == inamht
                                      && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper() || strMatngan.Length == 0)
                                    select new
                                    {
                                        p.ID_LOG,
                                        p.MA_DVIQLY,
                                        p.MA_KHANG,
                                        p.ID_HDON,
                                        p.THANG_HT,
                                        p.NAM_HT,
                                        p.MA_TNGAN,
                                        p.SO_BBGIAO,
                                        p.NGAY_CHAMNO,
                                        p.TRANGTHAI,
                                        p.GIO,
                                        p.NGUOI_TAO,
                                        NGAY_TAO = p.NGAY_TAO.ToString("dd/MM/yyyy"),
                                    }
                                    ).ToList();
                var npc_chamnooffline = (from p in temp
                                        select p).ToList();
                npc_chamnooffline.Clear();
                if (Danhsach == 0) // tất cả danh sách
                {
                    if (IN_BC == 0)//theo ngày
                    {
                        npc_chamnooffline = (from p in temp
                                                  where p.NGAY_TAO == pNgayChamNo
                                                  //&& p.NGAY_CHAMNO <= pNgaythu_den
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                    if (IN_BC == 1) //theo thời điểm
                    {
                        npc_chamnooffline = (from p in temp
                                                  where p.GIO >= tugio
                                                  && p.GIO <= dengio
                                                  //&& p.TRANGTHAI == 0
                                                  select p).ToList();
                    }
                }
                //if (Danhsach == 1) // danh sách theo mã khách hàng
                //{
                //    if (IN_BC == 0) // theo ngày
                //    {
                //        npc_chamnooffline_log_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE_LOG
                //                                  where p.MA_DVIQLY == strMadvi
                //                                  && p.MA_KHANG == ma_khang
                //                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                //                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper())
                //                                  && p.NGAY_TAO.Date == pNgayChamNo
                //                                  //&& p.TRANGTHAI == 0
                //                                  select p).ToList();
                //    }
                //}
                //if (Danhsach == 2)// theo id_hdon
                //{
                //    if (IN_BC == 0)
                //    {
                //        npc_chamnooffline_log_list = (from p in CMIS2.DB.NPC_CHAMNO_OFFLINE_LOG
                //                                  where p.MA_DVIQLY == strMadvi
                //                                  && p.ID_HDON == id_hdon
                //                                  && p.THANG_HT == ithanght && p.NAM_HT == inamht
                //                                  && (p.MA_TNGAN.ToUpper() == strMatngan.ToUpper())
                //                                  && p.NGAY_TAO.Date == pNgayChamNo
                //                                  //&& p.TRANGTHAI == 0
                //                                  select p).ToList();
                //    }
                //}
                
                arrID_HDON = npc_chamnooffline.Select(c => c.ID_HDON).ToArray();

                var lstno = (from p in CMIS2.DB.CN_THEODOINO
                             where p.MA_DVIQLY == strMadvi
                             && arrID_HDON.Contains(p.ID_HDON)
                             select p).ToList();
                var KQ = (from m in npc_chamnooffline
                          join n in lstno
                          on new { m.MA_DVIQLY, m.ID_HDON } equals new { n.MA_DVIQLY, n.ID_HDON }
                          orderby n.MA_SOGCS, n.STT
                          select new
                          {
                              m.MA_DVIQLY,
                              m.ID_HDON,
                              m.MA_TNGAN,
                              TEN_TNGAN = (from k in d_tnganvien_lst where k.MA_TNGAN == m.MA_TNGAN select k.TEN_TNGAN).FirstOrDefault(),
                              m.SO_BBGIAO,
                              m.THANG_HT,
                              m.NAM_HT,
                              TIEN_NO = n.TIEN_PSINH,
                              THUE_NO = n.THUE_PSINH,
                              TONGTIEN = n.TIEN_PSINH + n.THUE_PSINH,
                              m.NGAY_CHAMNO,
                              NGAY_GIOHTHONG = m.NGAY_TAO,
                              n.MA_KHANG,
                              n.TEN_KHANG,
                              n.DCHI_KHANG,
                              n.KY,
                              n.THANG,
                              n.NAM,
                              n.MA_SOGCS,
                              n.STT,
                              n.LOAI_HDON
                          }
                          ).ToList();
                dt = Utility.LINQToDataTable(KQ);
                dt.TableName = "DSTHUTIEN";
                ds.Tables.Add(dt);
                return ds;
            }
            catch
            { return null; }
        }
        #endregion
    }
}
